package app.client.collect.cartoon;

import app.client.collect.cartoon.entity.CartoonCollectEntity;
import cn.lily.com.container.data.Record;
import cn.lily.com.db.container.reader.DataTable;
import cn.lily.com.db.sql.DBManager;
import cn.lily.com.db.sql.manager.DBOfflineManager;
import cn.lily.com.enviroment.Enviroment;
import cn.lily.com.file.excel.css.ExcelWriterStyle;
import cn.lily.com.file.excel.io.ExcelReader;
import cn.lily.com.file.excel.io.ExcelWriter;
import cn.lily.com.file.excel.spe.IExcelColor;
import cn.lily.com.lang.Basic;

/**
 * 采集分析类
 * @author JiaJ
 * @date 2018年1月8日
 *
 */
public class CollectAnaylse {
	
	/**
	 * Excel读取
	 */
	private ExcelReader reader;
	
	/**
	 * Excel输出
	 */
	private ExcelWriter writer;
	
	/**
	 * 数据库连接
	 */
	private DBManager manager;
	
	private ExcelWriterStyle titleStyle;
	private ExcelWriterStyle errorDataStyle;
	
	private int analyseCount = 0;
	private int successCount = 0;
	
	/**
	 * 构造
	 * @param reader
	 */
	public CollectAnaylse(String filePath,String analysePath){
		
		this.reader = new ExcelReader(filePath);
		this.writer = new ExcelWriter(analysePath);
		this.manager = DBOfflineManager.getDBManager();
		
		this.writer.inst();
		
		this.writer.createSheet();
		this.writer.renameSheet("无匹配记录");
		
		this.writer.createSheet();
		this.writer.renameSheet("集数年份错误");
		
		this.createExcelTitle();
		
	}
	
	private void createExcelTitle(){
		
		titleStyle = new ExcelWriterStyle(this.writer);
		titleStyle.getCell().setCellAlign("center").setCellVetical("center").setBackGroundColor(IExcelColor.GREY_40_PERCENT);
		
		errorDataStyle = new ExcelWriterStyle(this.writer);
		errorDataStyle.getCell().setCellAlign("center").setCellVetical("center").setBackGroundColor(IExcelColor.RED);
		
		this.writer.locateSheet(0);
		this.writer.locateRow(0);
		
		this.writer.setCellValue(0, "动漫名称");
		this.writer.setColWith(0, 250);
		this.writer.setStyle(titleStyle);
		this.writer.setCellValue(1, "动漫季数");
		this.writer.setColWith(1, 100);
		this.writer.setStyle(titleStyle);
		this.writer.setCellValue(2, "动漫集数");
		this.writer.setColWith(2, 100);
		this.writer.setStyle(titleStyle);
		this.writer.setCellValue(3, "动漫年份");
		this.writer.setColWith(3, 100);
		this.writer.setStyle(titleStyle);
		this.writer.setCellValue(4, "动漫地区");
		this.writer.setColWith(4, 100);
		this.writer.setStyle(titleStyle);
		this.writer.setCellValue(5, "动漫状态");
		this.writer.setColWith(5, 100);
		this.writer.setStyle(titleStyle);
		
		this.writer.freezeFirstRow();
		
		this.writer.locateSheet(1);
		this.writer.locateRow(0);
		
		this.writer.setCellValue(0, "动漫名称");
		this.writer.setColWith(0, 250);
		this.writer.setStyle(titleStyle);
		this.writer.setCellValue(1, "动漫季数");
		this.writer.setColWith(1, 100);
		this.writer.setStyle(titleStyle);
		this.writer.setCellValue(2, "动漫集数");
		this.writer.setColWith(2, 100);
		this.writer.setStyle(titleStyle);
		this.writer.setCellValue(3, "动漫年份");
		this.writer.setColWith(3, 100);
		this.writer.setStyle(titleStyle);
		this.writer.setCellValue(4, "动漫地区");
		this.writer.setColWith(4, 100);
		this.writer.setStyle(titleStyle);
		this.writer.setCellValue(5, "动漫状态");
		this.writer.setColWith(5, 100);
		this.writer.setStyle(titleStyle);
		this.writer.setCellValue(6, "异常数量");
		this.writer.setColWith(6, 200);
		this.writer.setStyle(titleStyle);
		this.writer.setCellValue(7, "异常年份");
		this.writer.setColWith(7, 200);
		this.writer.setStyle(titleStyle);
		
		this.writer.freezeFirstRow();
	}
	
	/**
	 * 分析
	 */
	public void analyse(){
		
		int sheetCount = this.reader.getSheetCount();
		
		for(int i=0;i<sheetCount;i++){
			
			this.reader.locateSheet(i);
			
			for(int j=0;j<=this.reader.getRowNum()[1];j++){
				
				this.reader.locateRow(j);
				
				if(j != 0){
					
					CartoonCollectEntity entity = new CartoonCollectEntity();
					
					entity.setCartoonName(this.reader.getCellValue(0));
					entity.setArea(this.reader.getCellValue(4));
					entity.setCartoonNum(Basic.getInt(this.reader.getCellValue(2)));
					entity.setSeason(this.reader.getCellValue(1));
					entity.setYear(this.reader.getCellValue(3));
					entity.setStatus(this.reader.getCellValue(5));
					
					this.analyseEntity(entity);
					
				}
				
			}
			
		}
		
	}
	
	private void analyseEntity(CartoonCollectEntity entity){
		
		this.analyseCount ++ ;
		
		//名称检索
		String canNam = entity.getCartoonName();
		
		System.out.println("【"+canNam+"】正在分析.............");
		
		int canNum = entity.getCartoonNum();
		if(canNum == 0){
			return;
		}

		//初遍历
		String nameCommonSql = "select * from LEO_CAN_INFO where CAN_NAME_MEMO like '%"+canNam+"%'";
		DataTable nameCommonTable = this.manager.executeQueryTable(nameCommonSql);
		int nameCommonCount = nameCommonTable.getRecordCount();
		
		//无匹配
		if(nameCommonCount <= 0){
			
			String nameDetailSql = "select * from LEO_CAN_DETAIL_INFO where CAN_DETAIL_NAME like '%"+canNam+"%' ";
			
			DataTable nameDetailTable = this.manager.executeQueryTable(nameDetailSql);
			
			//匹配明细表
			this.matchDetailTable(entity, nameDetailTable);
			
		} 
		
		//匹配一条
		else if(nameCommonCount == 1){
			
			nameCommonTable.next();
			
			String canID = nameCommonTable.getString("CAN_ID");
			
			String nameDetailSql = "select * from LEO_CAN_DETAIL_INFO where CAN_ID='"+canID+"' ";
			
			DataTable nameDetailTable = this.manager.executeQueryTable(nameDetailSql);
			
			//匹配明细表
			this.matchDetailTable(entity, nameDetailTable);
			
		} 
		
		//匹配多条
		else {
			
			String canIDs = "";
			while(nameCommonTable.next()){
				canIDs += ",'"+nameCommonTable.getString("CAN_ID")+"'";
			}
			canIDs = canIDs.substring(1);
			
			String nameDetailSql = "select * from LEO_CAN_DETAIL_INFO where CAN_ID in ("+canIDs+") ";
			
			DataTable nameDetailTable = this.manager.executeQueryTable(nameDetailSql);
			
			//匹配明细表
			this.matchDetailTable(entity, nameDetailTable);
			
		}
		
		
	}
	
	private void matchDetailTable(CartoonCollectEntity entity,DataTable nameDetailTable){
		
		String canNam = entity.getCartoonName();
		
		int nameDetailCount = nameDetailTable.getRecordCount();
		
		//无匹配记录情况，录入无匹配处理
		if(nameDetailCount <= 0){				
			this.setNoMatchRecord(entity);			
			return;
		}
		
		else {
			
			String season = entity.getSeason();
			
			while(nameDetailTable.next()){
				
				String canDetailName = nameDetailTable.getString("CAN_DETAIL_NAME");
				
				if("1".equals(season)){
					
					//记录完全匹配
					if(canDetailName.indexOf("第1") >= 0 || canDetailName.indexOf("第一") >= 0 || 
							canDetailName.indexOf(canNam+"1")>=0 || canDetailName.indexOf(canNam+" 1")>=0 || 
							canDetailName.indexOf("I季")>=0 || canDetailName.indexOf("I部")>=0 ){
						
						//匹配成功记录处理
						this.matchSuccess(entity, nameDetailTable.getRecord());
						return;
						
					}
					
					else if(canDetailName.indexOf("第") >= 0){
						continue;
					}
					
					//记录可能匹配
					else {
						
						//匹配成功记录处理
						this.matchSuccess(entity, nameDetailTable.getRecord());	
						return;
						
					}
					
				}
				
				else if("SP".equals(season)){
					
					String canKind = nameDetailTable.getString("CAN_KIND");
					
					if("06".equals(canKind)){
						//匹配成功记录处理
						this.matchSuccess(entity, nameDetailTable.getRecord());
						return;
					}else{
						continue;
					}
					
				}
				
				else if("OVA".equals(season)){
					
					String canKind = nameDetailTable.getString("CAN_KIND");
					
					if("02".equals(canKind)){
						//匹配成功记录处理
						this.matchSuccess(entity, nameDetailTable.getRecord());
						return;
					}else{
						continue;
					}
					
				}
				
				//其他季数处理
				else {
					
					String[] XFix = {"I","II","III","IV","V","VI","VII","VIII","IX","X"};
					String[] CFix = {"一","二","三","四","五","六","七","八","九","十"};
					
					int seasonIdx = Basic.getInt(season);
							
					//记录完全匹配
					if(canDetailName.indexOf("第"+season) >= 0  || canDetailName.indexOf(canNam+season)>=0
							|| canDetailName.indexOf(canNam+" "+season)>=0){
						
						//匹配成功记录处理
						this.matchSuccess(entity, nameDetailTable.getRecord());
						return;
						
					}
					
					else if(seasonIdx<=10 && seasonIdx>=1 && (canDetailName.indexOf(XFix[seasonIdx-1]+"季")>=0 
							               || canDetailName.indexOf(XFix[seasonIdx-1]+"部")>=0 
							               || canDetailName.indexOf(CFix[seasonIdx-1]+"季")>=0 
							               || canDetailName.indexOf(CFix[seasonIdx-1]+"部")>=0 )){
						//匹配成功记录处理
						this.matchSuccess(entity, nameDetailTable.getRecord());
						return;
						
					}
					
					else {
						continue;
					}
					
				}
				
			}
			
			//无匹配记录，录入数据
			this.setNoMatchRecord(entity);
			return;
			
		}
		
	}
	
	/**
	 * 匹配成功处理
	 */
	private void matchSuccess(CartoonCollectEntity entity,Record detailRecord){
		
		String canNam = entity.getCartoonName();
		String canYear = entity.getYear();
		
		String canDetailTime = detailRecord.getString("CAN_TIME");
		int canDetailNum = Basic.getInt(detailRecord.getString("CAN_TOTAL"));
		int canNum = entity.getCartoonNum();
		
		//时间不确定
		if(canYear.startsWith("#")){
			
			//匹配成功
			if(canDetailNum == canNum){
				this.successCount++;
				return;
			}
			
			//数量匹配少，默认也成功
			else if(canDetailNum > canNum){
				this.successCount++;
				return;
			}
			
			//数量不对，记录数量异常
			else{								
				this.setNumErrorRecord(entity, canDetailNum,"");		
				return;
			}
			
		}
		
		//时间确定
		else{
			
			//年份准确
			if(canDetailTime.startsWith(canYear)){
				
				//匹配成功
				if(canDetailNum == canNum){
					this.successCount++;
					return;
				}
				
				//数量匹配少，默认也成功
				else if(canDetailNum > canNum){
					this.successCount++;
					return;
				}
				
				//数量不对，记录数量异常
				else{								
					this.setNumErrorRecord(entity, canDetailNum,"");		
					return;
				}
				
			}
			
			//年份不对
			else {
				
				//匹配成功
				if(canDetailNum == canNum){
					this.setNumErrorRecord(entity, 0,canDetailTime);		
					return;
				}
				
				//数量匹配少，默认也成功
				else if(canDetailNum > canNum){
					this.setNumErrorRecord(entity, 0,canDetailTime);		
					return;
				}
				
				//数量不对，记录数量异常
				else{								
					this.setNumErrorRecord(entity, canDetailNum,canDetailTime);		
					return;
				}
				
			}
			
		}
		
	}
	
	/**
	 * 设置无匹配记录
	 * @param entity
	 */
	private void setNoMatchRecord(CartoonCollectEntity entity){		
		this.writer.locateSheet(0);		
		this.writer.createRow();	
		this.writer.setCellValue(0, entity.getCartoonName());
		this.writer.setCellValue(1, entity.getSeason());
		this.writer.setCellValue(2, entity.getCartoonNum()+"");
		this.writer.setCellValue(3, entity.getYear());
		this.writer.setCellValue(4, entity.getArea());
		this.writer.setCellValue(5, entity.getStatus());	
	}
	
	/**
	 * 设置数量错误记录
	 * @param entity
	 * @param errorNum
	 */
	private void setNumErrorRecord(CartoonCollectEntity entity,int errorNum,String errorYear){
		this.writer.locateSheet(1);		
		this.writer.createRow();	
		this.writer.setCellValue(0, entity.getCartoonName());
		this.writer.setCellValue(1, entity.getSeason());
		this.writer.setCellValue(2, entity.getCartoonNum()+"");
		this.writer.setCellValue(3, entity.getYear());
		this.writer.setCellValue(4, entity.getArea());
		this.writer.setCellValue(5, entity.getStatus());

		if(errorNum == 0){
			this.writer.setCellValue(6, "");
		}else{
			this.writer.setCellValue(6, errorNum+"");
			this.writer.setStyle(6,errorDataStyle);
		}
		
		if("".equals(errorYear)){
			this.writer.setCellValue(7, "");
		}else{
			this.writer.setCellValue(7, errorYear+"");
			this.writer.setStyle(7,errorDataStyle);
		}
		
		
	}
	
	/**
	 * 输出信息
	 */
	public void write(){
		
		System.out.println();
		System.out.println();
		System.out.println();
		
		System.out.println("文件输出,总数："+this.analyseCount+",成功数："+this.successCount);
		
		this.writer.write();
	}
	
	/**
	 * 释放
	 */
	public void free(){
		this.reader.free();
		this.writer.free();
		this.manager.free();
	}
	
	public static void main(String[] args) {
		
		Enviroment.createEnv();
		Enviroment.getEnv().getLogBean().beginLog();
		
		CollectAnaylse analyse = new CollectAnaylse("D:/export.xlsx","D:/analyse.xlsx");
		
		analyse.analyse();
		
		analyse.write();
		
		analyse.free();
		
	}
	
}
